USE [CX_DB_Local]
GO

/****** Object:  StoredProcedure [dbo].[sprocProviderProposalSelectItem_Liberty]    Script Date: 07/02/2010 18:35:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sprocProviderProposalSelectItem_Liberty]
/*	'===============================================================
	'   NAME:                	[sprocProviderProposalSelectItem_Liberty] 455, 'CI'
	'   DATE CREATED:       	02 Jul 2010
	'   CREATED BY:          	KandasamySakthivel
	'   CREATED FOR:         	http://www.ComparExpress.com 
	'   FUNCTION:            	Get the list of item from table: tblCarMoreDetail_Liberty
	'   IN:                  
	'   OUT: 		Nothing         
	'   VERSION: 1           
	'   EXAMPLE:             
	'   COMMENTS:            
	'   MODIFIED ON:         
	'   MODIFIED BY:         
	'   REASON MODIFICATION: 
	'===============================================================*/
	@RefId			bigint
	,@InsuranceType	varchar(2)
	AS
	IF(@InsuranceType = 'CI')
	BEGIN
		SET NOCOUNT ON
		SELECT	CL.Name, CL.UnitNo, CL.HomeNo, CL.[Address], CL.PostalCode, CL.OfficePhoneNo, CL.MobileNo, CL.EmpNameandTrade, CL.NRICNo,
				CL.Nationality, CL.VechicleNo, CL.DateofRegistration, CL.EngineNo, CL.ChassisNo, CL.HasHirePurchase,
				CL.HasPreInsurer, CL.PreInsVechicleNo, CL.PreInsExpiryDate, CL.NCDEntitlement, CL.HasVechicleModified, CL.VechicleModifyReason,
				CL.HasAccessory, CL.AccessoryDescription, CL.HasConvictedMoterOffence, CL.ConvictedReason, CL.HasDemeritPoints,
				CL.HasPhysicalImpairment, CL.PhysicalImpairmentReason, CL.HasInsuranceTerminated, CL.InsuranceTerminatedReason,
				UP.DOB,OI.[Description],CU.Email,CP.IsClaimLastThreeYear, CD.RegYear,UP.YearDriveSg,
					DrWMalaysia = CASE	WHEN CL.HasDriveWestMalaysia = '-1' THEN '' 
										WHEN CL.HasDriveWestMalaysia = 'Weekly' THEN 'Weekly'
										WHEN CL.HasDriveWestMalaysia = 'Monthly' THEN 'Monthly'
										WHEN CL.HasDriveWestMalaysia = 'Less than monthly' THEN 'Less than monthly' END,
					Marital		= CASE	WHEN UP.MaritalStatus = 1 THEN 'Single' 
										WHEN UP.MaritalStatus = 2 THEN 'Married' 
										WHEN UP.MaritalStatus = 4 THEN 'Widowed' 
										WHEN UP.MaritalStatus = 5 THEN 'Divorced' END,
					Gender		= CASE	WHEN UP.Gender = 'M' THEN 'Male' 
										WHEN UP.Gender = 'F' THEN 'Female' END, 
					NofBusi		= CASE	WHEN UP.OccupationType = 1 THEN 'Mainly Indoor' 
										WHEN UP.OccupationType = 2 THEN 'Mainly Outdoor' END,
					
					Offbeak		= CASE	WHEN CD.IsPeak = 1 THEN 'Yes' 
										WHEN CD.IsPeak = 0 THEN 'No' END, 
					CP.CoverageCommenceDate AS StartDate, DATEADD(YEAR,1,CP.CoverageCommenceDate-1) AS EndDate, CS.PlanName,
					AddDriver	= CASE	WHEN CP.IsAddDriver = 1 THEN 'Yes' 
										WHEN CP.IsAddDriver = 0 THEN 'No' END,
					CmpName		= CASE	WHEN CL.PreInsurerName = 0 THEN 'Not Applicable' 
										WHEN CL.PreInsurerName IS NULL THEN 'Not Applicable' END,
					CL.NameoftheCard, CL.IssuingBankName, CL.CreditCardNo, CL.CardExpiryMonth, CL.CardExpiryYear, CL.CardType,CP.NCDPercent,
					CP.NoOfDriver,CL.PreInsPolicyNo, CL.PreInsExpiryDate
			FROM	tblCarMoreDetail_Liberty CL 
					INNER JOIN tblCardetail CD ON CL.CECarId = CD.CECarId 
					INNER JOIN tblCEUsrProfile UP ON CD.RefCEUserId = UP.ProfileID 
					INNER JOIN tblOccupationInfo OI ON UP.OccupationType = OI.OccupationId
					INNER JOIN tblCarPolicy CP ON CP.CECarId = CD.CECarId
					INNER JOIN tblCarInsuranceSelectItem CS ON CD.CECarId = CS.CECarId AND CS.[Status] = 1 
					INNER JOIN tblCEUser CU ON CD.RefCEUserId = CU.CEID 
			WHERE CL.CECarId = @RefId 
			
			
			SELECT	CONVERT(VARCHAR,(DATEADD(YEAR,-1,CP.ModifiedDate)),103) AS Claim1stStartDate, 
					CONVERT(VARCHAR,CP.ModifiedDate-1,103) AS Claim1stEndDate, CC.NOCLast1Value,CC.NOCLast1,
					CONVERT(VARCHAR,(DATEADD(YEAR,-2,CP.ModifiedDate)),103) AS Claim2ndStartDate, 
					CONVERT(VARCHAR,(DATEADD(YEAR,-1,CP.ModifiedDate-1)),103) AS Claim2ndEndDate, CC.NOCLast2Value,CC.NOCLast2,
					CONVERT(VARCHAR,(DATEADD(YEAR,-3,CP.ModifiedDate)),103) AS Claim3rdStartDate, 
					CONVERT(VARCHAR,(DATEADD(YEAR,-2,CP.ModifiedDate-1)),103) AS Claim3rdEndDate, CC.NOCLast3Value,CC.NOCLast3
			FROM	tblCarClaimHistory CC, tblCarPolicy CP 
			WHERE	CP.CECarId = CC.CECarId  AND CP.CECarId = @RefId AND CC.[Status] = 'Activ'

			SELECT	[CompanyName] AS InsCompanyName FROM tblPreInsurer PIN, tblCarMoreDetail_Etiqa CE 
			WHERE	CE.CECarId = @RefId AND CE.PreInsurerName = PIN.PreInsurerId AND PIN.CompanyType = 1 AND PIN.ServiceType = 100

			SELECT	[CompanyName] AS HPCompanyName FROM tblPreInsurer PIN, tblCarmoreDetail_Etiqa CE
			WHERE	CE.CECarId = @RefId AND CE.HPCompanyName = PIN.PreInsurerId AND PIN.CompanyType = 2 AND PIN.ServiceType = 100

			SELECT (CDR.Salutation + '. ' + CDR.Surname) AS DriverName, [DOB],OI.[Description],CDR.YearOfDrivingInSg
			FROM	tblCarDriver CDR INNER JOIN tblCardetail CD ON CD.CECarId = CDR.CECarId 
					AND CDR.[Status] = 'Active' AND CD.CECarId = @RefId
					INNER JOIN tblOccupationInfo OI ON CDR.Occupation = OI.OccupationId
					
			SELECT	CMI.[Description],CMI.[CC],CMI.[Body], CMO.[Description] AS Make FROM tblCarModelInfo CMI 
					INNER JOIN tblCarDetail CDL ON CMI.CarMakeId = CDL.CarMakeId AND CMI.CarModelId = CDL.CarModelId AND CDL.CECarId = @RefId
					INNER JOIN tblCarMakeInfo CMO ON CDL.CarMakeId = CMO.CarMakeId
					
			SELECT  DriverName, TotalDemeritPoints,CONVERT(VARCHAR,DemeritDate,103) AS DemeritDate, TypeofOffence
			FROM	tblCarDriverDemeritPoints 
			WHERE CECarID = @RefId AND [Status] = 1
	END
	
	
GO

/****** Object:  StoredProcedure [dbo].[sprocProviderProposalSelectItem_Etiqa]    Script Date: 07/02/2010 18:35:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sprocProviderProposalSelectItem_Etiqa]
/*	'===============================================================
	'   NAME:                	[sprocProviderProposalSelectItem_Etiqa] 299, 'CI'
	'   DATE CREATED:       	02 Jul 2010
	'   CREATED BY:          	KandasamySakthivel
	'   CREATED FOR:         	http://www.ComparExpress.com 
	'   FUNCTION:            	Get the list of item from table: tblCarMoreDetail_Etiqa
	'   IN:                  
	'   OUT: 		Nothing         
	'   VERSION: 1           
	'   EXAMPLE:             
	'   COMMENTS:            
	'   MODIFIED ON:         
	'   MODIFIED BY:         
	'   REASON MODIFICATION: 
	'===============================================================*/
	@RefId			bigint
	,@InsuranceType	varchar(2)
	AS
	IF(@InsuranceType = 'CI')
	BEGIN
		SET NOCOUNT ON
		SELECT	CE.Name,UP.DOB, CE.UnitNo,CE.HomeNo,CE.[Address],CE.PostalCode,CE.ContactNo,CE.NRICNo,CE.Nationality,CE.VechicleNo,CE.EngineNo,
				CE.ChassisNo,CE.HasHirePurchase,CE.HasPreInsurer,CE.HasDemeritPoints,CE.HasInsuranceTerminated,CE.InsuranceTerminatedReason,
				CE.HasPhysicalImpairment,CE.PhysicalImpairmentReason, OI.[Description],CU.Email,CP.IsClaimLastThreeYear, CD.RegYear,UP.YearDriveSg,
					Marital		= CASE	WHEN UP.MaritalStatus = 1 THEN 'Single' 
										WHEN UP.MaritalStatus = 2 THEN 'Married' 
										WHEN UP.MaritalStatus = 4 THEN 'Widowed' 
										WHEN UP.MaritalStatus = 5 THEN 'Divorced' END,
					Gender		= CASE	WHEN UP.Gender = 'M' THEN 'Male' 
										WHEN UP.Gender = 'F' THEN 'Female' END, 
					NofBusi		= CASE	WHEN UP.OccupationType = 1 THEN 'Mainly Indoor' 
										WHEN UP.OccupationType = 2 THEN 'Mainly Outdoor' END,
					
					Offbeak		= CASE	WHEN CD.IsPeak = 1 THEN 'Yes' 
										WHEN CD.IsPeak = 0 THEN 'No' END, 
					CP.CoverageCommenceDate AS StartDate, DATEADD(YEAR,1,CP.CoverageCommenceDate-1) AS EndDate, CS.PlanName,
					AddDriver	= CASE	WHEN CP.IsAddDriver = 1 THEN 'Yes' 
										WHEN CP.IsAddDriver = 0 THEN 'No' END,
					CmpName		= CASE	WHEN CE.PreInsurerName = 0 THEN 'Not Applicable' 
										WHEN CE.PreInsurerName IS NULL THEN 'Not Applicable' END,
					CE.NameoftheCard, CE.IssuingBankName, CE.CreditCardNo, CE.CardExpiryMonth, CE.CardExpiryYear, CE.CardType,CP.NCDPercent,
					CP.NoOfDriver,CE.PreInsPolicyNo, CE.PreInsExpiryDate
			FROM	tblCarMoreDetail_Etiqa CE 
					INNER JOIN tblCardetail CD ON CE.CECarId = CD.CECarId 
					INNER JOIN tblCEUsrProfile UP ON CD.RefCEUserId = UP.ProfileID 
					INNER JOIN tblOccupationInfo OI ON UP.OccupationType = OI.OccupationId
					INNER JOIN tblCarPolicy CP ON CP.CECarId = CD.CECarId
					INNER JOIN tblCarInsuranceSelectItem CS ON CD.CECarId = CS.CECarId AND CS.[Status] = 1 
					INNER JOIN tblCEUser CU ON CD.RefCEUserId = CU.CEID 
			WHERE CE.CECarId = @RefId 
			
			
			SELECT	CONVERT(VARCHAR,(DATEADD(YEAR,-1,CP.ModifiedDate)),103) AS Claim1stStartDate, 
					CONVERT(VARCHAR,CP.ModifiedDate-1,103) AS Claim1stEndDate, CC.NOCLast1Value,CC.NOCLast1,
					CONVERT(VARCHAR,(DATEADD(YEAR,-2,CP.ModifiedDate)),103) AS Claim2ndStartDate, 
					CONVERT(VARCHAR,(DATEADD(YEAR,-1,CP.ModifiedDate-1)),103) AS Claim2ndEndDate, CC.NOCLast2Value,CC.NOCLast2,
					CONVERT(VARCHAR,(DATEADD(YEAR,-3,CP.ModifiedDate)),103) AS Claim3rdStartDate, 
					CONVERT(VARCHAR,(DATEADD(YEAR,-2,CP.ModifiedDate-1)),103) AS Claim3rdEndDate, CC.NOCLast3Value,CC.NOCLast3
			FROM	tblCarClaimHistory CC, tblCarPolicy CP 
			WHERE	CP.CECarId = CC.CECarId  AND CP.CECarId = @RefId AND CC.[Status] = 'Activ'

			SELECT	[CompanyName] AS InsCompanyName FROM tblPreInsurer PIN, tblCarMoreDetail_Etiqa CE 
			WHERE	CE.CECarId = @RefId AND CE.PreInsurerName = PIN.PreInsurerId AND PIN.CompanyType = 1 AND PIN.ServiceType = 100

			SELECT	[CompanyName] AS HPCompanyName FROM tblPreInsurer PIN, tblCarmoreDetail_Etiqa CE
			WHERE	CE.CECarId = @RefId AND CE.HPCompanyName = PIN.PreInsurerId AND PIN.CompanyType = 2 AND PIN.ServiceType = 100

			SELECT (CDR.Salutation + '. ' + CDR.Surname) AS DriverName, [DOB],OI.[Description],CDR.YearOfDrivingInSg
			FROM	tblCarDriver CDR INNER JOIN tblCardetail CD ON CD.CECarId = CDR.CECarId 
					AND CDR.[Status] = 'Active' AND CD.CECarId = @RefId
					INNER JOIN tblOccupationInfo OI ON CDR.Occupation = OI.OccupationId
					
			SELECT	CMI.[Description],CMI.[CC],CMI.[Body], CMO.[Description] AS Make FROM tblCarModelInfo CMI 
					INNER JOIN tblCarDetail CDL ON CMI.CarMakeId = CDL.CarMakeId AND CMI.CarModelId = CDL.CarModelId AND CDL.CECarId = @RefId
					INNER JOIN tblCarMakeInfo CMO ON CDL.CarMakeId = CMO.CarMakeId
					
			SELECT  DriverName, TotalDemeritPoints
			FROM	tblCarDriverDemeritPoints 
			WHERE CECarID = @RefId AND [Status] = 1
	END
GO

/****** Object:  StoredProcedure [dbo].[sprocProviderProposalItemList]    Script Date: 07/02/2010 18:35:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sprocProviderProposalItemList]
/*	'===============================================================
	'   NAME:                	[sprocProviderProposalItemList] 23, 'CI'
	'   DATE CREATED:       	01 Jul 2010
	'   CREATED BY:          	KandasamySakthivel
	'   CREATED FOR:         	http://www.ComparExpress.com 
	'   FUNCTION:            	Get the list of item from table: tblCarMoreDetail_Hsbc or tblCarMoreDetail_Etiqa or tblCarMoreDetail_Liberty
	'   IN:                  
	'   OUT: 		Nothing         
	'   VERSION: 1           
	'   EXAMPLE:             
	'   COMMENTS:            
	'   MODIFIED ON:         
	'   MODIFIED BY:         
	'   REASON MODIFICATION: 
	'===============================================================*/
	@ProviderCompanyId   bigint
	,@InsuranceType	     varchar(2)
	AS

	IF(@ProviderCompanyId = 23)
	BEGIN
		IF(@InsuranceType = 'CI')
		BEGIN
			SET NOCOUNT ON
			SELECT FamilyName as ProposerName, CONVERT(VARCHAR,ModifiedDate,106) AS PostedDate, CECarId, CECarId AS RefId 
			FROM tblCarMoreDetail_Hsbc WHERE ProviderCompanyId = @ProviderCompanyId AND [Status] = 1 ORDER BY CECarMoreDetailId DESC
		END -- ([Salutation] + '. ' + [GivenName]) as ProposerName,
	END
	ELSE IF(@ProviderCompanyId = 30)
	BEGIN
		IF(@InsuranceType = 'CI')
		BEGIN
			SET NOCOUNT ON
			SELECT Name as ProposerName, CONVERT(VARCHAR,ModifiedDate,106) AS PostedDate, CECarId, CECarId AS RefId 
			FROM tblCarMoreDetail_Etiqa WHERE ProviderCompanyId = @ProviderCompanyId AND [Status] = 1 ORDER BY CECarMoreDetailId DESC
		END -- ([Salutation] + '. ' + [GivenName]) as ProposerName,
	END













GO

/****** Object:  StoredProcedure [dbo].[sprocProviderProposalSelectItem_Hsbc]    Script Date: 07/02/2010 18:35:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sprocProviderProposalSelectItem_Hsbc]
/*	'===============================================================
	'   NAME:                	[sprocProviderProposalSelectItem_Hsbc] 420, 'CI'
	'   DATE CREATED:       	01 Jul 2010
	'   CREATED BY:          	KandasamySakthivel
	'   CREATED FOR:         	http://www.ComparExpress.com 
	'   FUNCTION:            	Get the list of item from table: tblCarMoreDetail_Hsbc
	'   IN:                  
	'   OUT: 		Nothing         
	'   VERSION: 1           
	'   EXAMPLE:             
	'   COMMENTS:            
	'   MODIFIED ON:         
	'   MODIFIED BY:         
	'   REASON MODIFICATION: 
	'===============================================================*/
	@RefId			bigint
	,@InsuranceType	varchar(2)
	AS
	IF(@InsuranceType = 'CI')
	BEGIN
		SET NOCOUNT ON
		SELECT	CH.FamilyName,CH.GivenName,UP.DOB,CH.Nationality, CH.UnitNo,CH.HomeNo,CH.[Address],CH.PostalCode,CH.NRICNo, 
				Marital		= CASE	WHEN UP.MaritalStatus = 1 THEN 'Single' 
									WHEN UP.MaritalStatus = 2 THEN 'Married' 
									WHEN UP.MaritalStatus = 4 THEN 'Widowed' 
									WHEN UP.MaritalStatus = 5 THEN 'Divorced' END,
				Gender		= CASE	WHEN UP.Gender = 'M' THEN 'Male' 
									WHEN UP.Gender = 'F' THEN 'Female' END, 
				NofBusi		= CASE	WHEN UP.OccupationType = 1 THEN 'Mainly Indoor' 
									WHEN UP.OccupationType = 2 THEN 'Mainly Outdoor' END,
				OI.[Description],[MobileNo], [HomePhoneNo], [OfficePhoneNo], [FaxNo],[EmpName],CU.Email,CP.IsClaimLastThreeYear, CH.NCDReason,
				CH.NCDOtherReason, CH.VechicleNo, CD.RegYear, CH.PurchasePrice,CH.EngineNo, CH.ChassisNo, CH.VechicleModifyReason,
				Offbeak		= CASE	WHEN CD.IsPeak = 1 THEN 'Yes' 
									WHEN CD.IsPeak = 0 THEN 'No' END, CH.EngineType,
				VehclModify = CASE	WHEN CH.HasVechicleModified = 0 THEN 'No' 
									WHEN CH.HasVechicleModified = 1 THEN 'Yes' END, 
				HirePurchase =CASE	WHEN CH.HasHirePurchase = 0 THEN 'No' 
									WHEN CH.HasHirePurchase = 1 THEN 'Yes' END,
				CP.CoverageCommenceDate AS StartDate, DATEADD(YEAR,1,CP.CoverageCommenceDate-1) AS EndDate, CS.PlanName,
				AddDriver	= CASE	WHEN CP.IsAddDriver = 1 THEN 'Yes' 
									WHEN CP.IsAddDriver = 0 THEN 'No' END,
				CmpName		= CASE	WHEN CH.InsCompanyName = 0 THEN 'Not Applicable' END,
				CH.NameoftheCard, CH.IssuingBankName, CH.CreditCardNo, CH.CardExpiryMonth, CH.CardExpiryYear, CH.CardType,CP.NCDPercent,
				CP.NoOfDriver
		FROM	tblCarMoreDetail_Hsbc CH 
				INNER JOIN tblCardetail CD ON CH.CECarId = CD.CECarId 
				INNER JOIN tblCEUsrProfile UP ON CD.RefCEUserId = UP.ProfileID 
				INNER JOIN tblOccupationInfo OI ON UP.OccupationType = OI.OccupationId
				INNER JOIN tblCarPolicy CP ON CP.CECarId = CD.CECarId
				INNER JOIN tblCarInsuranceSelectItem CS ON CD.CECarId = CS.CECarId AND CS.[Status] = 1 
				INNER JOIN tblCEUser CU ON CD.RefCEUserId = CU.CEID 
		WHERE CH.CECarId = @RefId 
		 
		 SELECT	CONVERT(VARCHAR,(DATEADD(YEAR,-1,CP.ModifiedDate)),103) AS Claim1stStartDate, 
				CONVERT(VARCHAR,CP.ModifiedDate-1,103) AS Claim1stEndDate, CC.NOCLast1Value,CC.NOCLast1,
				CONVERT(VARCHAR,(DATEADD(YEAR,-2,CP.ModifiedDate)),103) AS Claim2ndStartDate, 
				CONVERT(VARCHAR,(DATEADD(YEAR,-1,CP.ModifiedDate-1)),103) AS Claim2ndEndDate, CC.NOCLast2Value,CC.NOCLast2,
				CONVERT(VARCHAR,(DATEADD(YEAR,-3,CP.ModifiedDate)),103) AS Claim3rdStartDate, 
				CONVERT(VARCHAR,(DATEADD(YEAR,-2,CP.ModifiedDate-1)),103) AS Claim3rdEndDate, CC.NOCLast3Value,CC.NOCLast3
		FROM tblCarClaimHistory CC, tblCarPolicy CP 
		WHERE CP.CECarId = CC.CECarId  AND CP.CECarId = @RefId AND CC.[Status] = 'Activ'

		 SELECT	[CompanyName] AS InsCompanyName FROM tblPreInsurer PIN, tblCarmoreDetail_Hsbc CH 
		 WHERE	CH.CECarId = @RefId AND CH.InsCompanyName = PIN.PreInsurerId AND PIN.CompanyType = 1 AND PIN.ServiceType = 100
		  
		 SELECT	[CompanyName] AS HPCompanyName FROM tblPreInsurer PIN, tblCarmoreDetail_Hsbc CH 
		 WHERE	CH.CECarId = @RefId AND CH.HPCompanyName = PIN.PreInsurerId AND PIN.CompanyType = 2 AND PIN.ServiceType = 100
		 
		SELECT (CDR.Salutation + '. ' + CDR.Surname) AS DriverName, [DOB],OI.[Description],
				DvrBusiness	= CASE	WHEN CDR.JobNature = 'INDOOR'	THEN 'Mainly Indoor' 
									WHEN CDR.JobNature = 'OUTDOOR'	THEN 'Mainly Outdoor' END
		FROM	tblCarDriver CDR INNER JOIN tblCardetail CD ON CD.CECarId = CDR.CECarId 
				AND CDR.[Status] = 'Active' AND CD.CECarId = @RefId
				INNER JOIN tblOccupationInfo OI ON CDR.Occupation = OI.OccupationId
				
		SELECT CMI.[Description],CMI.[CC],CMI.[Body] FROM tblCarModelInfo CMI 
				INNER JOIN tblCarDetail CDL ON CMI.CarMakeId = CDL.CarMakeId AND CMI.CarModelId = CDL.CarModelId AND CDL.CECarId = @RefId
	END
GO


